// .........................................................................
// Title: wrds_characteristics.do
//
// Imports data on bond characteristics from WRDS
// .........................................................................

* --------------------------------------
* Import WRDS ratings
* --------------------------------------

* bond ratings from wrds
use "$raw/wrds/wrds_bond_returns.dta", clear
keep DATE CUSIP R_* N_* RATING_NUM RATING_CAT
gen date_m = mofd(DATE)
format %tm date_m
rename CUSIP cusip
drop if missing(cusip)
gsort cusip date_m -DATE
by cusip date_m: keep if _n == 1
drop DATE
rename RATING_NUM rating_num
rename RATING_CAT rating_cat
order cusip date_m

* convert rating num to category
gen rating_cat_pm = ""
replace rating_cat_pm = "AAA" if rating_num == 1
replace rating_cat_pm = "AA+" if rating_num == 2
replace rating_cat_pm = "AA" if rating_num == 3
replace rating_cat_pm = "AA-" if rating_num == 4
replace rating_cat_pm = "A+" if rating_num == 5
replace rating_cat_pm = "A" if rating_num == 6
replace rating_cat_pm = "A-" if rating_num == 7
replace rating_cat_pm = "BBB+" if rating_num == 8
replace rating_cat_pm = "BBB" if rating_num == 9
replace rating_cat_pm = "BBB-" if rating_num == 10
replace rating_cat_pm = "BB+" if rating_num == 11
replace rating_cat_pm = "BB" if rating_num == 12
replace rating_cat_pm = "BB-" if rating_num == 13
replace rating_cat_pm = "B+" if rating_num == 14
replace rating_cat_pm = "B" if rating_num == 15
replace rating_cat_pm = "B-" if rating_num == 16
replace rating_cat_pm = "CCC+" if rating_num == 17
replace rating_cat_pm = "CCC" if rating_num == 18
replace rating_cat_pm = "CCC-" if rating_num == 19
replace rating_cat_pm = "CC" if rating_num == 20
replace rating_cat_pm = "C" if rating_num == 21
replace rating_cat_pm = "D" if rating_num == 22

* save it
save "$tmp/wrds_bond_ratings", replace

* collapse to a yearly frequency
use "$tmp/wrds_bond_ratings", clear
gen year = year(dofm(date_m))
assert ~missing(rating_cat) if ~missing(rating_num)
gen _nonmissing_rating = 0
replace _nonmissing_rating = 1 if ~missing(rating_cat)
gsort cusip year
by cusip year: egen nonmissing_rating = max(_nonmissing_rating)
drop _nonmissing_rating
drop if nonmissing_rating == 0
by cusip year: egen modal_rating_cat = mode(rating_cat), maxmode
by cusip year: egen modal_rating_cat_pm = mode(rating_cat_pm), maxmode
by cusip year: keep if _n == 1
keep cusip year modal_rating_cat modal_rating_cat_pm
rename modal_* *
save "$tmp/wrds_bond_ratings_y", replace
